# -*- coding: utf-8 -*-
import re
import sqlite3
import numpy as np
from flask import Flask, jsonify
from flask import render_template

app = Flask(__name__)


@app.route('/')
def index():
    return render_template('index.html')


def parse_question_fetch_answer(question):
    """问题解析成 sql 语句"""

    conn = sqlite3.connect('all_house_infos.db')
    cursor = conn.cursor()
    info = ''

    # 基于规则的 sql 解析与查询结果
    pattern = re.compile('(多少小区)|(有哪些小区)')
    results = re.findall(pattern, question)
    if len(results) > 0:
        sql = 'select distinct 所属小区 from  HouseInfo'
        print('==> EXECUTE SQL: {}'.format(sql))
        cursor.execute(sql)
        datas = cursor.fetchall()
        top_xiaoqus = [d[0] for d in datas[:40]]
        info = '当前数据库中一共包含 {} 个小区数据，包括：{}等'.format(len(datas), '、'.join(top_xiaoqus))
        print(info)

    pattern = re.compile('(.+)小区的均价')
    results = re.findall(pattern, question)
    if len(results) > 0:
        xiaoqu = results[0]
        sql = "select 总价 from HouseInfo where 所属小区 like '{}'".format(xiaoqu)
        print('==> EXECUTE SQL: {}'.format(sql))
        cursor.execute(sql)
        datas = cursor.fetchall()
        mean_price = float(np.mean([d[0] for d in datas]))
        info = "{}小区的均价为 {:.2f} 元。".format(xiaoqu, mean_price)
        print(info)

    pattern = re.compile('(.+).*(的建造年代|是哪一年建造的)')
    results = re.findall(pattern, question)
    if len(results) > 0:
        xiaoqu = results[0][0]
        sql = "select 建造年代 from HouseInfo where 所属小区 like '{}'".format(xiaoqu)
        print('==> EXECUTE SQL: {}'.format(sql))
        cursor.execute(sql)
        datas = cursor.fetchall()
        years = []
        for d in datas:
            d = d[0][:-1]
            try:
                years.append(int(d))
            except:
                pass
        year = int(np.mean(years))
        info = "{}小区的建造年代为 {} 年。".format(xiaoqu, int(year))
        print(info)

    pattern = re.compile('(.+)小区.*配套电梯')
    results = re.findall(pattern, question)
    if len(results) > 0:
        xiaoqu = results[0]
        sql = "select 配套电梯 from HouseInfo where 所属小区 like '{}'".format(xiaoqu)
        print('==> EXECUTE SQL: {}'.format(sql))
        cursor.execute(sql)
        datas = cursor.fetchall()
        datas = [d[0] for d in datas]
        info = "{}小区的有配套电梯的房子 {} 套，没有配套电梯的房子 {} 套".format(xiaoqu, datas.count('有'), datas.count('无'))
        print(info)

    pattern = re.compile('(.+)小区.*一手房源')
    results = re.findall(pattern, question)
    if len(results) > 0:
        xiaoqu = results[0]
        sql = "select 一手房源 from HouseInfo where 所属小区 like '{}'".format(xiaoqu)
        print('==> EXECUTE SQL: {}'.format(sql))
        cursor.execute(sql)
        datas = cursor.fetchall()
        datas = [d[0] for d in datas]
        print(datas)
        info = "{}小区一共有 {} 套房，其中是一手房源的房子 {} 套，不是一手房源的房子 {} 套".format(xiaoqu, len(datas), datas.count('是'), datas.count('否'))
        print(info)

    pattern = re.compile('(.+).*的产权性质')
    results = re.findall(pattern, question)
    if len(results) > 0:
        xiaoqu = results[0]
        sql = "select 产权性质 from HouseInfo where 所属小区 like '{}'".format(xiaoqu)
        print('==> EXECUTE SQL: {}'.format(sql))
        cursor.execute(sql)
        datas = cursor.fetchall()
        datas = [d[0] for d in datas]
        sub_info = ''
        for key in set(datas):
            count = datas.count(key)
            sub_info += '{}房子有 {} 套'.format(key, count)

        info = "{}小区一共有 {} 套房，其中{}。".format(xiaoqu, len(datas), sub_info)
        print(info)

    pattern = re.compile('(.+).*在哪儿')
    results = re.findall(pattern, question)
    if len(results) > 0:
        xiaoqu = results[0]
        sql = "select 经度, 纬度 from HouseInfo where 所属小区 like '{}'".format(xiaoqu)
        print('==> EXECUTE SQL: {}'.format(sql))
        cursor.execute(sql)
        datas = cursor.fetchall()
        jingdu = [d[0] for d in datas]
        weidu = [d[1] for d in datas]
        jingdu = float(np.mean(jingdu))
        weidu = float(np.mean(weidu))
        info = "{}小区的经度：{:.4f}，维度: {:.4f}，您可以使用百度地图查看具体方位。".format(xiaoqu, jingdu, weidu)
        print(info)

    return info


@app.route('/bot_action/<question>', methods=['GET'])
def bot_action(question):
    """
    依据问题检索 document
    """
    answer = parse_question_fetch_answer(question)

    if answer == '':
        return {'answer': '抱歉小房没有理解您的问题。'}
    else:
        result = {'answer': answer}
        return jsonify(result)


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=False)
